﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using Northwind.NET.Model;
using System.Data.Common;
using System.Collections;
using Alachisoft.NosDB.Common;

namespace Northwind.NET.EDM.FE.WinForms.Controls
{
    public partial class CustomerOrdersUserControl : BaseUserControl
    {
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            LoadCustomers();
            LoadOrders();
        }

        private void LoadOrders()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;

                string cname = nameTextBox.Text;
                string country = countryTextBox.Text;
                string customerId = "";
                using (DbConnection innerConnection = Factory.CreateConnection())
                {
                    innerConnection.ConnectionString = ConnectionString;
                    innerConnection.Open();
                    DbCommand innerCommand = Factory.CreateCommand();
                    innerCommand.Connection = innerConnection;
                    innerCommand.CommandText = "SELECT ID FROM customers  WHERE ContactName='" + cname + "' AND Country = '" + country + "'";

                    customerId = (string)innerCommand.ExecuteScalar();
                }
                

                command.CommandText = "SELECT ID, OrderDate, RequiredDate, ShippedDate FROM Orders WHERE CustomerID = '" + customerId + "'";

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Orders");
                adapter.Fill(dataset.Tables["Orders"]);

                this.orderDataGridView.DataSource = dataset.Tables["Orders"];
            }
        }
        void NavigatorMouseUp(object sender, System.Windows.Forms.MouseEventArgs e)
        {
            LoadCustomers();
            LoadOrders();
        }
        private void LoadCustomers()
        {
            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                DbDataAdapter adapter = Factory.CreateDataAdapter();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT Name, Country, ContactName FROM Customers";

                adapter.SelectCommand = command;

                DataSet dataset = new DataSet();
                dataset.Tables.Add("Customers");
                adapter.Fill(dataset.Tables["Customers"]);

                this.customerBindingSource.DataSource = dataset.Tables["Customers"];
            }
        }
        public CustomerOrdersUserControl()
        {
            InitializeComponent();
        }

        public override string FormCaption { get { return Settings.Resources.CustomerOrdersFormCaption; } }
        public override dynamic GetEntitySet(ObjectContext context)
        {
            return new Model.Customer();
        }

        public override BindingSource UserControlBindingSource { get { return customerBindingSource; }}

        private bool _rowEnterCalled;
        private void orderDataGridView_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (_rowEnterCalled) return;
            try
            {
                _rowEnterCalled = true;
                const int orderIdCellIndex = 0;
                DataGridViewRow row = orderDataGridView.Rows[e.RowIndex];
                int orderId = row.Cells[orderIdCellIndex].Value != null ? Convert.ToInt32(row.Cells[orderIdCellIndex].Value) : 0;

                setOrderDetailsDataSource(orderId.ToString());
            }
            finally
            {
                _rowEnterCalled = false;
            }
        }

        private void setOrderDetailsDataSource(string orderIdStr)
        {
            int orderId = -1;
            bool ret = Int32.TryParse(orderIdStr, out orderId);

            string query = "SELECT Freight, OrderDetails FROM Orders WHERE ID = '" + orderId + "'";

            using (DbConnection connection = Factory.CreateConnection())
            {
                double totalExtendedPrice = 0.0;
                double freight = 0.0;
                connection.ConnectionString = ConnectionString;
                connection.Open();
                DbCommand command = Factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = query;

                DataSet set = new DataSet();
                set.Tables.Add("OrderDetails");
                set.Tables["OrderDetails"].Columns.Add("Name", typeof(string));
                set.Tables["OrderDetails"].Columns.Add("UnitPrice", typeof(double));
                set.Tables["OrderDetails"].Columns.Add("Quantity", typeof(long));
                set.Tables["OrderDetails"].Columns.Add("Discount", typeof(double));
                set.Tables["OrderDetails"].Columns.Add("ExtendedPrice", typeof(double));
                IDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    freight = dr.GetDouble(0);
                    ArrayList orderDetails = new ArrayList((object[])dr.GetValue(1));

                    IEnumerator ie = orderDetails.GetEnumerator();
                    while (ie.MoveNext())
                    {
                        JSONDocument document = (JSONDocument)ie.Current;

                        string productName = "";
                        using (DbConnection innerConnection = Factory.CreateConnection())
                        {
                            innerConnection.ConnectionString = ConnectionString;
                            innerConnection.Open();
                            DbCommand innerCommand = Factory.CreateCommand();
                            innerCommand.Connection = innerConnection;
                            innerCommand.CommandText = "SELECT Name from Products WHERE ID = '" + document["ProductID"] + "'";

                            productName = (string)innerCommand.ExecuteScalar();
                        }
                        double extendedprice = ((double)document["UnitPrice"] * (long)document["Quantity"] * (1 - (double)document["Discount"]));
                        totalExtendedPrice += extendedprice;
                        set.Tables["OrderDetails"].Rows.Add(productName, document["UnitPrice"], document["Quantity"], document["Discount"], extendedprice);
                    }
                }
                this.customerOrdersProductsDataGridView.DataSource = set.Tables["OrderDetails"];
                //this.totalExtendedPriceTextBox.Text = totalExtendedPrice.ToString("C2");
                //this.orderTotalTextBox.Text = (totalExtendedPrice + freight).ToString("C2");
            }
        }
    }
}
